
function Test-IsAdmin {
    $local:backuperror = $ErrorActionPreference
    $ErrorActionPreference = "silentlycontinue"
    $result = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")
    $ErrorActionPreference = $local:backuperror
    If ([Environment]::UserName -eq "FarmAccountSamAccountName") {$result=$true}  #when logging on as the farm account, no run as admin is needed. you can enter a farm account here 
    Return ,$result

}


Function Log-text{
  [CmdletBinding()]
  param
  (
    [Parameter(Mandatory=$false)]
    [string]$Logtext,
    [switch]$verboseLogging,
    [switch]$OnlyToScreen,
    [switch]$IsError
  )
  
If(($global:LogMode -eq "normal") -and ($verboseLogging -eq $true)){return}

    If(($global:logToScreenOnly -eq $false)-and ($OnlyToScreen-eq $false)){$logtext | add-content $global:Logfile}
    If($global:LogToScreen -eq $true){
            if ($IsError -eq $false){
                write-Host $Logtext -ForegroundColor Yellow
            }else{
                write-Host $Logtext -ForegroundColor Red
            }
        } 
}


if(!(Test-IsAdmin)){
    $local:Currentuser = [Environment]::UserName
    Write-Host "You are user:" $local:Currentuser -ForegroundColor magenta
    write-host "SharePoint scripts must be run as admin!" -ForegroundColor magenta
    write-host "(You must also be Farm Admin or have SP scripting privileges)" -ForegroundColor magenta
    write-host 
    write-host "SharePoint Functions NOT LOADED!" -ForegroundColor red
    exit
    }

Function global:ADD-PATH()
{
    [Cmdletbinding()]
    param
    (
    [parameter(Mandatory=$True,
    ValueFromPipeline=$True,
    Position=0)]
    [String[]]$AddedFolder
    )
    #ADD-PATH "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN"
# Get the current search path from the environment keys in the registry.
    $local:backuperroract = $ErrorActionPreference
    $ErrorActionPreference = "silentlycontinue"
    $OldPath=(Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\Environment' -Name PATH).Path
    IF (!$AddedFolder){ Return ‘No Folder Supplied. $ENV:PATH Unchanged’}
    IF (!(TEST-PATH $AddedFolder)){ Return ‘Folder Does not Exist, Cannot be added to $ENV:PATH’ }
    IF ($ENV:PATH | Select-String -SimpleMatch $AddedFolder){ Return ‘Folder already within $ENV:PATH' }
    if(!(Test-IsAdmin)){ Return ‘You are not an admin, setting path failed.' }
    $NewPath=$OldPath+’;’+$AddedFolder
    Set-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\Environment' -Name PATH –Value $newPath
    if($? -eq $True){
        $ErrorActionPreference =$local:backuperroract 
        Return $True
    }else{
        $ErrorActionPreference =$local:backuperroract 
        Return $false
    }
}

Function Sp-GetSPSitesFromDatabase{
<#
.SYNOPSIS
 Find all sites belonging to a specific database

.DESCRIPTION
 Returns all sites that belong to a specified database.
 Input is the Name of the database as returned by get-spcontentdatabase

.EXAMPLE
Sp-GetSPSitesFromDatabase -Name WSS_Content_MySitesADFS



#>
[Cmdletbinding()]
param
(
  [parameter(Mandatory=$true,ValueFromPipelineByPropertyName,ValueFromPipeline)]
  [string]$Name  
)


$backuperror=$ErrorActionPreference
$ErrorActionPreference="silentlycontinue"
$db = Get-SPContentDatabase $Name  
If($? -eq $true){
    $result = get-spsite -Limit all | where {$_.ContentDatabase.Id.Guid -eq $($db.id)}
    $ErrorActionPreference=$backuperror
    Return, $result
}else{
     "Database $name not found" | out-host
    $ErrorActionPreference=$backuperror
    Return, "Database $name not found"
    }
}



Function Sp-FindDBWithMultipleCollections{
<#
.SYNOPSIS
    Find databases with multiple site collections

.DESCRIPTION
    Returns an array with all site urls and the database that hosts them.
    You can use move-spsite to move the database to a different content database

.EXAMPLE

$Wrongsites= Sp-FindDBWithMultipleCollections
$wrongsites

Site url1                      Site url2                      Database                     
---------                      ---------                      --------                     
https://Site1.contoso.com https://site2.contoso.com SPContentDatabase Name=contosodb

Move-SPSite -Identity $Wrongsites[0].'Site url1' -DestinationDatabase "WSS_Content_MySites-prov1"

#>
$FaultyMySites=@()
write-host "Searching content databases for multiple collections..."
 $SharepointDBs = Get-SPDatabase | where{(($_.type -like "content database") -and ($_.name -notlike "WSS*") -and ($_.name -notlike "SharePoint_AdminContent*"))}
 $global:WebsNotInUse=@()
 $backuperr=$ErrorActionPreference
 $ErrorActionPreference="silentlycontinue"
    Foreach($dbtest in $SharepointDBs){
        $site= get-spsite -ContentDatabase $dbtest.id -Limit all
            if($site -is [system.array]){
               #$site.url 
               #$dbtest.Name
               $ReturnArray = New-Object psobject 
               $inc=0
               foreach($sitefound in $site){
                 $Inc+=1
                  $ReturnArray | Add-Member NoteProperty -Name "Site url$Inc" -Value "$($sitefound.url)"
               }
               $ReturnArray | Add-Member NoteProperty -Name "Database" -Value $dbtest
               $FaultyMySites+=$ReturnArray
            }
        }
    If($FaultyMySites.count -gt 0){
    Return, $FaultyMySites
    }else{
    Return, "No multiple collections in content databases"
    }
}


Function SP-GetEmptyContentDatabases {
<#
.SYNOPSIS
 Find all provisioned content databases in SharePoint that contain no sites

.DESCRIPTION
 Returns all SQL databases starting with the name "team" or "project" who do not contain sharepoint content databases.

.EXAMPLE
SP-GetEmptyContentDatabases



#>
Write-host "Due to the use of invoke command, this may take a while" -ForegroundColor Yellow

    # Invoke command op de win526 #
    Write-host "Using DB Server $Global:DBServer" -ForegroundColor Yellow
    Write-host "Querying server...."
    $result = Invoke-Command -ComputerName $Global:DBServer -ScriptBlock {

    # Load assemblies #
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

    # Primary Server van Sharepoint AG database #
    $DBserver = $Global:DBServer
    $SQLserver = New-Object Microsoft.SqlServer.Management.Smo.Server $DBserver
    $SQLserver.Databases
    }
    Write-host "Comparing sql databases to sharepoint active content databases"
        # All databases op SQL server: #

        $SqlDBs = $result.name | where{(($_ -like "Team*") -or ($_ -like "Project*"))}
        $SqlDBs = $SqlDBs | ? {$_}

        $SharepointDBs = $(Get-SPDatabase).name | where {($_ -like "Team*") -or ($_ -like "Project*")}

        $DatabasesNotInUse = @()
        $Differences = Compare-Object $SharepointDBs $SqlDBs
            foreach ($differenceobject in $Differences){
                
                $DatabasesNotInUse += $differenceobject.inputobject
                
            }
Return, $DatabasesNotInUse
} 


Function SP-Initialise{
$error.clear()
$local:backuperroract = $ErrorActionPreference
$ErrorActionPreference = "silentlycontinue"



[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Portal")
Add-PSSnapin "Microsoft.SharePoint.PowerShell"

$Global:Server = "https://FarmAddress.contoso.com"  #use the fqdn
$Global:mysiteHostUrl = $Global:Server + "/my" #change MY to the name you use as a mysite root.
$Global:DBServer = "SQLDBInstanceNameOfYourSharePointEnvironment" #enter the database instance name where sharepoint connects to.
$Global:mysiteHost = Get-SPSite $Global:mysiteHostUrl 
$datetime=get-date -Format yyyyMMddHHmmss
$global:Logfile="$datetime-SharePoint-Functions.log"
$global:LogMode="normal"  #normal or verbose
$global:LogToScreen = $true
$global:logToScreenOnly = $false
$local:AddPathResult=ADD-PATH "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN"
if($local:AddPathResult -eq $true) {Write-Host "Path to SharePoint binary files was added. please restart powershell!" -ForegroundColor Magenta}

$Global:ClaimsPrefixArray = @{}
$Global:ClaimsPrefixArray["Windowsclaim"] = @{}
$Global:ClaimsPrefixArray["Windowsauthclaim"] = @{}
$Global:ClaimsPrefixArray["Samlsclaim"] = @{}
$Global:ClaimsPrefixArray["fbaclaim"] = @{}
$Global:ClaimsPrefixArray["Windowsclaim"] ="i:0#.w|ad\" 
$Global:ClaimsPrefixArray["WindowsAuthClaim"] ="c:0!.s|ad\" 
$Global:ClaimsPrefixArray["SamlClaim"] ="i:05.t|Other User|" 
$Global:ClaimsPrefixArray["fbaClaim"] ="i:0#.f|" 

$Global:mydbWarningSiteCount = 480
$Global:mydbMaximumSiteCount = 500
$Global:mysiteNewDbThreshold = 100 #MySite threshold
$ErrorActionPreference = $local:backuperroract
If($error.Count -gt 0){ 
    return ,$error
   }else{
   return, $true
  }

}




Function SP-RemoveMySite{

<#
  .SYNOPSIS
  Remove MySite for a user
 
  .DESCRIPTION
 Remove a My Site for a user. The user will not be removed from the User Profile Service database. Sp-RemoveMySite only accepts SamAccountNames as input. please be carefull as there is no 'undo' option!
 
 Return value is the outcome of the removal command.

 After removing the MySite, please wait a few minutes before re-adding a MySite using SP-CreateMySite to allow SharePoint to cleanup the database.
 
  .EXAMPLE
   Sp-RemoveMySite -identity "User1"
 
  
  #>


    param
    (
    [Parameter(Mandatory=$true)]
    [string]$identity
    )

 $site=new-object Microsoft.SharePoint.SPSite($Global:mysiteHostUrl);            
 try            
 {            
  $serviceContext = Get-SPServiceContext $site;            
  $upm = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($serviceContext);             
  if($upm.UserExists($identity) -eq $false)            
  {            
   Write-Host "User $identity was not found in the profile store." -f yellow;            
   return;            
  }else
  {            
    $userProfile = $upm.GetUserProfile($identity);            
        if($userProfile.PersonalSite -ne $Null){            
            Write-Host "Mysite found for user $identity" -f yellow;
            Write-Host "Trying to remove personal site." -f yellow;
           $local:result= $userProfile.PersonalSite.Delete()
        }  
    }
  }
  catch            
 {            
  Write-Host "Encountered an error removing a MySite for user $identity. Error:"$_.Exception -f Red;            
 }  
 


return ,$local:result
}



Function SP-GetProfileInfo{

<#
  .SYNOPSIS
 Returns all properties for a user that are in the User Profile Server Database,
 or $false when no profile is found in the database.
 
  .DESCRIPTION
 Returns all properties for a user that are in the User Profile Server Database.
 This can be very helpfull to identify problems with a SharePoint user profile or my site.
 
 SP-GetProfileInfo only accepts one SamAccountName as input.

  .EXAMPLE
   SP-GetProfileInfo -identity "User1"
 
  
  #>
    param
    (
    [Parameter(Mandatory=$true)]
    [string]$Identity
    )


 $site=new-object Microsoft.SharePoint.SPSite($Global:mysiteHostUrl);            
 try            
 {            
  $serviceContext = Get-SPServiceContext $site;            
  $upm = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($serviceContext);             
  if($upm.UserExists($Identity) -eq $false)            
  {            
   Write-Host "User $Identity was not found in the profile store." -f yellow;            
   return, $false;            
  }else
  {            
    $userProfile = $upm.GetUserProfile($Identity);            
    }
  }
  catch            
 {            
  Write-Host "Encountered an error getting profile info for user $Identity. Error:"$_.Exception -f Red;            
 }  
 
 
return , $userProfile
}


Function SP-CreateMySite{
<#
  .SYNOPSIS
  Create MySite for a user
 
  .DESCRIPTION
 Create a My Site for a user
 Sp-CreateMySite only accepts SamAccountNames as input.

 
  .EXAMPLE
   Sp-CreateMySite -identity "User1"
 
  
  #>


    param
    (
    [Parameter(Mandatory=$true)]
    [string]$identity
    )

   
    $site=new-object Microsoft.SharePoint.SPSite($Global:mysiteHostUrl); 
    $Context = Get-SPServiceContext $site
    $upm =  New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

    #Create user profile
    $profile = $upm.GetUserProfile($identity)


        if(!$profile)
        {
        Write-Host "$profile does not have a profile. Can't create personal site"
        }
        elseif($profile){
            if($profile.PersonalSite -eq $Null){
             $profile.CreatePersonalSiteEnque($false) 
                if ($? -eq $true){
                Write-Host "Personal site created"
            }else{
                Write-Host " Error creating site"
            }

        }
        else        {
        Write-Warning "$username already has a personal site"
        }
     }
}

Function Sp-FindSite{
 <#
 .SYNOPSIS
  Searches all site collections for partial names
 
  .DESCRIPTION
 Finds all sites with the name entered
 #>
 param
    (
    [Parameter(Mandatory=$true)]
    [string]$Sitename,
    [switch]$Exact
    )

    If($exact -eq $false){
        $Found = get-spsite -limit all | where {$_.url -like "*$sitename*"}
    return, $Found
    }else
    {
      $Found = get-spsite -limit all | where {$_.url -like "*$sitename"}
      return, $Found
    }
}


Function Sp-CreateMySitesFromCSV{


<#
  .SYNOPSIS
  Create MySites for users based on an csv import file. 
 
  .DESCRIPTION
 Create MySites from an importfile.
 You should use a file of type "#TYPE Selected.Microsoft.ActiveDirectory.Management.ADUser"
  Please export samaccountnames to a csv file like this:
  get-aduser -Filter {company -eq "ut-icts"} -SearchBase $Global:Searchbase -SearchScope OneLevel -Properties company | select samaccountname | Export-Csv "filename.csv"
  
  .EXAMPLE
  get-aduser -Filter {company -eq "ut-icts"} -SearchBase $Global:Searchbase -SearchScope OneLevel -Properties company | select samaccountname | Export-Csv "filename.csv"
  Sp-CreateMySitesFromCSV -filename "filename.csv"
 
  
  #>
  [CmdletBinding()]
  param
  (
    [Parameter(Mandatory=$True)]
    [string]$filename,
    [switch]$Whatif 
	
  )

    $local:backuperroract = $ErrorActionPreference
    $ErrorActionPreference = "silentlycontinue"
    $local:fileexists = Test-Path $filename
    $ErrorActionPreference = $local:backuperroract
    $local:CreationCounter=0
    if($local:fileexists -eq $true){
    
        $local:array = Import-Csv $filename
        $local:max = $local:array.Count
        $local:count=0

        foreach($local:user in $local:array){
        $local:count+=1
        Write-Progress -Activity "Provision users from csv" -Status "$local:count from $local:max"  -CurrentOperation $($local:user.samaccountname) -PercentComplete (( $local:count/$local:max)*100)
         If($WhatIf -eq $true){
           $testprofile= SP-GetProfileInfo -Identity $($local:user.samaccountname)
            if(($testprofile.PersonalSiteInstantiationState) -ne "Created"){
                write-host "What if: creating mysite for $($local:user.samaccountname)" -ForegroundColor Yellow
            }Else{
                write-host "What if:mMySite already exists for $($local:user.samaccountname)" -ForegroundColor green
            }
         }
         Else{
         
            $testprofile= SP-GetProfileInfo -Identity $($local:user.samaccountname)
            if(($testprofile.PersonalSiteInstantiationState) -ne "Created"){
                write-host "creating mysite for $($local:user.samaccountname)" -ForegroundColor magenta
             $($local:user.samaccountname)
                SP-CreateMySite -identity $local:user.samaccountname
                $local:CreationCounter+=1
            }else
            {
                  write-host "profile already exists for $($local:user.samaccountname)" -ForegroundColor green
            }
            if((($local:CreationCounter % 10) -eq 0)-and($local:CreationCounter -ne 0)){
                Write-Progress -Activity "Provision users from csv" -Status "$local:count from $local:max"  -CurrentOperation "Pausing for 3 minutes" -PercentComplete (( $local:count/$local:max)*100) 
                start-sleep -Seconds 180 
            }
         }
        }
    }
 }


Function SP-CheckContentDatabaseForUpgrade{
<#
  .SYNOPSIS
  Returns the number of databases to upgrade after applying new SharePoint Patches.
 
  .DESCRIPTION
  Never alters any databases. Use this command to get a list of all databases that have the "needsupgrade' entry set to true
  Use SP-UpgradeAllContentDatabases to start upgrading the databases

  .EXAMPLE

  To get the number of databases to upgrade, enter:
  CheckContentDatabaseForUpgrade

  .EXAMPLE

  To use it in a variable, enter:

  $MyVar = SP-CheckContentDatabaseForUpgrade

 #>
  
$local:AllDbs
    Write-Host "Fetching all Content Databases..." -ForegroundColor Yellow
    $local:AllDbs = Get-SPDatabase | Where {$_.Type -eq "Content Database"}
    Write-Host "Found $($local:AllDbs.count) databases"
    $local:needupgradecount=0
    Foreach($db in $local:AllDbs){
        If($db.NeedsUpgrade -eq $true){
          Write-host "$($db.name) needs to be upgraded. Current version:$($db.version)"
        $local:needupgradecount+=1
        }
    }
    write-host "Found $local:needupgradecount databases to upgrade"
    Return, $local:needupgradecount
}


Function SP-UpgradeAllContentDatabases{

 <#
  .SYNOPSIS
  Upgrades SharePoint Content databases after applying new SharePoint Patches.
 
  .DESCRIPTION
  Upgrades all content databases that have the 'needsupdate' value set to true.
  Databases that are in the process of being upgraded will not be accessible during upgrade.
  If you need to run the configuration wizard after an upgrade you can include the -IncludePsConfig switch. 
  The 'includePSConfig' is optional and can be omitted. When set, PSCONFIG.EXE is called on the local server. 
  However, when includePSConfig is set, you can add the -RunOnServer switch to run the PSCONFIG command on a remote server.

  .Parameter IncludePsConfig
  When added to the command, the PSCONFIG.EXE file will be ran after content databases are upgraded.

  .Parameter RunOnServer
  You can specify a servername to run the PSCONFIG.exe command on. PSCONFIG will then be executed on the remote server.
  Needs IncludePsConfig to be added also. 

  .EXAMPLE
  To upgrade all content databases only enter:

  SP-UpgradeAllContentDatabases

  .EXAMPLE

  To update all databases and run psconfig afterwards enter:

  SP-UpgradeAllContentDatabases -IncludePsConfig

   .EXAMPLE
  To update all databases and run psconfig afterwards on a remote server, enter:

  SP-UpgradeAllContentDatabases -IncludePsConfig -RunOnServer WINXXX


 #>  
     Param
    (
    [Parameter(Mandatory=$false)]
    [switch]$IncludePsConfig,
    [string]$RunOnServer="Localhost"
    )
  
    $PSConfig_Path = "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN\PSConfig.exe"
    $PSConfig_Params = '"-cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures"'
    $PsConfigCommand ={cd\;cd "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN";PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures;net start w3svc}
    $local:upgradecount = SP-CheckContentDatabaseForUpgrade
    if ($local:upgradecount -gt 0){
        Write-Host "Upgrading content and business data databases." -ForegroundColor Yellow
        $local:AllDbs = Get-SPDatabase | Where {$_.Type -eq "Content Database"}
        $local:Alldbs | Where {$_.needsupgrade -eq $true} | Upgrade-SPContentDatabase
        (Get-SPDatabase | ?{$_.type -eq "Microsoft.SharePoint.BusinessData.SharedService.BdcServiceDatabase"}).Provision()
        Write-Host "Done upgrading content and business data databases" -ForegroundColor Magenta
    }
    If($IncludePsConfig -eq $true) {
         Write-Host "Upgrading farm using PSConfig..." -ForegroundColor Yellow
         write-host "running command "$PSConfig_Path  $PSConfig_Params
         if($RunOnServer -eq "Localhost"){
           Write-host "Running on localhost" -ForegroundColor yellow
            Invoke-Command -ScriptBlock $PsConfigCommand
         }else

         {
            Write-host "Running on remote machine $RunOnServer" -ForegroundColor Yellow
            Invoke-Command -ScriptBlock $PsConfigCommand -ComputerName $RunOnServer
         }


    }

}



Function SP-ResetAllDBDefaults{

<#

  .SYNOPSIS
  Resets all database defaults to global settings in this script..
  All databases will be found using this script, including databases marked as "Offlene"
 
  .DESCRIPTION
  
  Resets values for warnings and maximum number of sites for all content databases.
  Warnings will be disabled, and the maximum number of subsites will be set to 1

  Note! All databases with "mysite" in the name will be skipped.
  
    
  .EXAMPLE

   SP-ResetAllDBDefaults

 #>


    

    $local:AllDbs = Get-SPDatabase | Where {$_.Type -eq "Content Database"} #haal alle databasesop.
    $local:AllDbs | ?({$_.name -notlike "*mysite*" -and $_.WebApplication[0].DisplayName -eq "sharepoint-portal"})| %{
        
        $_.WarningSiteCount = $null
        $_.MaximumSiteCount = $_.currentsitecount
        $_.update()
        Set-SPContentDatabase -Identity $_ -Status 1 
        Write-host "set defaults for site $($_.name)"        
        Write-host "set defaults for site $($_.webapplication)"        
        }

}



Function SP-SetAllContentDBOffline{
<#
  .SYNOPSIS
  Sets all content databases to status:offline.
  Mysite databases will always be skipped. Only databases with status "Online"  will be found by this function.
 
  .DESCRIPTION
  Sets all content databases to status offline for sharepoint so no additional site collections can be added to the database.
  The database will remain available but no new site collections can be made. This has no effect on the usage.
  you can still edit the site and add subsites.

  Also resets values for warnings and maximum number of sites for all content databases.
  Warnings will be disabled, and the maximum number of subsites will be set to 1
  
  Return value will be errors made during this script, or the number of reset sites if applicable
    
  .EXAMPLE
    
  SP-SetAllContentDBOffline

 #>
    $local:err =@()
    $resetcount=0
    $local:AllDbs = Get-SPContentDatabase #haal alleen de online content databases op.
    $local:AllDbs | ?{$_.name -notlike "*mysite*"} | %{
        $resetcount+=1
        $_.WarningSiteCount = $null
        $_.MaximumSiteCount = $_.currentsitecount
        Set-SPContentDatabase -Identity $_ -Status 1
        if($? -eq $false){$local:err+=$error[0]}
        Write-host "set defaults for site $($_.name)" 
        }
    if($local:err -gt 0) {Return, $local:err}else{return $resetcount}
    }


Function SP-ManageMySiteDBs{
<#
  .SYNOPSIS
   Manages all default values for MySite databases.

  .DESCRIPTION
  Resets all default values fore MySite databases to 500 maximum site users per database
  Also makes sure that warnings are set to 480,and that there is always room for 100 new users.
  If there are less than 100 free slots in the MySite databases, a new database will be created automatically.


  It is good practice to run this function regularly. It's completely safe to run this script as it is non-destructive.
  
 .RETURNVALUE 
   There is no return value for this function.

 #>
 $local:AllMySites = Get-SPContentDatabase | ?{$_.name -like "*mysite*"}
 $local:newdatabaseneeded=$false
 $local:FreeMySiteSlots=0
 $local:Sitecount=0
 Foreach($MyDB in $local:AllMySites){
    
    If (($MyDB.warningsitecount -gt $Global:mydbWarningSiteCount) -or ($MyDB.MaximumSiteCount -gt $Global:mydbMaximumSiteCount))
        {
           $mydb.WarningSiteCount = $Global:mydbWarningSiteCount
           $mydb.MaximumSiteCount = $Global:mydbMaximumSiteCount 
           $mydb.update()
           "Set $($mydb.name) to defaults" 
        }
        $local:Sitecount+=$mydb.currentsitecount
 }

 $local:FreeMySiteSlots = ($AllMySites.Count*$Global:mydbMaximumSiteCount)-$local:Sitecount
 write-host "Free slots for MySites: $local:FreeMySiteSlots"
 If ($local:FreeMySiteSlots -lt $Global:mysiteNewDbThreshold) {
    "Free slots is ($local:FreeMySiteSlots) Creating new database"        
    #als het aantal vrije slots voor mysites kleiner is dan de threshold bouwen we een nieuwe database voor mysites.
    $count = $local:AllMySites.count+1
    $name = "WSS_Content_MySites"+$count

    $result = New-SPContentDatabase -Name  $name -WebApplication $Global:Server -DatabaseServer $Global:DBServer -MaxSiteCount $Global:mydbMaximumSiteCount -WarningSiteCount  $Global:mydbWarningSiteCount 
    If($? -eq $false)

        { 
            Write-host "error creating MySite Database $Name"  -ForegroundColor Red
        }else {

            Write-host "Succesfully created MySite Database $name" -ForegroundColor Green
            Write-host $result
        }
 }  

}
 
Function Sp-GetAllSiteOwners{

$local:Result =  Get-SPWebApplication $webapplication | Get-SPSiteAdministration -Limit ALL | 
    where {$_.url -notlike "*/my/*" } | 
    select url,ownerdisplayname,owneremail,SecondaryContactEmail,SecondaryContactLoginName,description
Return ,$local:Result
}

Function SP-GetBuildNumber{
    $result = Get-SPFarm | Select BuildVersion
    Return, $result
}


Function SP-RunPSConfig{
 <#
  .SYNOPSIS
  Upgrades SharePoint config after applying new SharePoint Patches.
 
  .DESCRIPTION
  If you need to run the configuration wizard after an upgrade you can run this command. 
  The 'RunExtendedConfig' is optional and can be omitted. When set, PSCONFIG.EXE is called with extended settings to also upgrade applications.

  .Parameter RunExtendedConfig
  You can specify a servername to run the PSCONFIG.exe command on. PSCONFIG will then be executed on the remote server.
  Needs IncludePsConfig to be added also. 

  .EXAMPLE
  To upgrade the config database:

  SP-RunPSConfig
  Config command will be: Psconfig.exe -cmd upgrade -inplace b2b -wait -force

  .EXAMPLE

  To update all config application data also:

  SP-RunPSConfig -RunExtendedConfig
  Config command will be: PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures



 #> 



param(
[switch]$RunExtendedConfig
)
$caption = "Warning!"
$message = "Do you want to proceed?"
$yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes",""
$no = New-Object System.Management.Automation.Host.ChoiceDescription "&No",""
$choices = [System.Management.Automation.Host.ChoiceDescription[]]($yes,$no)

 Write-Host "Upgrading farm using PSConfig..." -ForegroundColor Yellow
 Write-host "Running on localhost" -ForegroundColor yellow
 $PScommand = "Psconfig.exe -cmd upgrade -inplace b2b -wait -force"
 $PSConfigCommand ={cd\;cd "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN";Psconfig.exe -cmd upgrade -inplace b2b -wait -force;}
 If($RunExtendedConfig -eq $true){
    $PsConfigCommand ={cd\;cd "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN";PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures;}
    write-host "Running extend command: PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures"  -ForegroundColor White 
 }else
 {
   write-host "Running upgrade command: $PScommand"  -ForegroundColor White 
 }
  $continue = $Host.UI.PromptForChoice($caption,$message,$choices,1)
  If($continue -eq 0){ 
  Invoke-Command -ScriptBlock $PSConfigCommand
  Write-Host "PSConfig completed." -ForegroundColor Yellow
  Write-host "Checking service W3SVC..." -ForegroundColor Yellow
  If(((get-service "w3svc").status) -ne "running"){Write-host "Starting service W3SVC..." -ForegroundColor white;start-service "w3svc"} 
  Write-host "RunPSConfig ended gracefully" -ForegroundColor Yellow
  }else{Write-host "RunPSConfig canceled" -ForegroundColor Yellow}
}



Function Sp-ClearConfigCache{
<#
  .SYNOPSIS
   Clears all temporary config files from cache.

  .DESCRIPTION
  Removes all .xml config files from the local cache and resets the counter to 1
  It is safe to run this command, it is not destructive. Once called, the config will be cleared on all application servers in the farm

  It is good practice to run this function before upgrading the farm and/or before running the psconfig wizard.
  A backup of the config xml cache will be available on the original location with the addition of the current date.
  
 .RETURNVALUE 
   There is no return value for this function.

 #>
#Fetches list of sharepoint servers in the farm.
Write-host "Fetching list of application servers for farm..."
$AppServers = Get-SPServer | Where-Object {$_.Role -eq "Application"}
write-host "SharePoint application servers are: $AppServers"

#Run following actions against each server in the farm
Foreach ($Server in $AppServers)
{
    #Fetches the individual server name from the list and inform user
    $ServerName = $Server.address
    Write-host "Performing action on server: $ServerName"

    #Stops timer service
    Write-host "Stopping timer service..."
    Get-Service SPTimerV4 -ComputerName $ServerName | Stop-Service

    #Gets list of folders in location which contains cache.ini file
    write-host "Proceeding to clear cache on the server..."
    $folders = Get-ChildItem "\\$ServerName\C$\ProgramData\Microsoft\SharePoint\Config" 
    foreach ($folder in $folders)
    {
        $items = Get-ChildItem $folder.FullName -Recurse
        foreach ($item in $items)
        {
            if ($item.Name.ToLower() -eq "cache.ini")
            {
                $cachefolder = $folder.FullName
            }
                
        }
    }
    
    #If folder contains cache.ini file, it contains configuration cache. Proceed with further actions.
    #TakeBackup of existing folder items
    $Destination = $Cachefolder + "_" + ($(Get-Date).ToShortDateString().Replace("/","_"))
    New-Item -Path $Destination -ItemType Directory -Force | Out-Null
    Copy-Item -Path $cachefolder -Destination $Destination -Recurse

    #Grab all .xml files and delete them
    $cachefolderitems = Get-ChildItem $cachefolder -Recurse
    foreach ($cachefolderitem in $cachefolderitems)
    {
         if ($cachefolderitem -like "*.xml")
         {
              $cachefolderitem.Delete()
         }
        
    }
    
    #Set value of cache.ini to 1
    write-host "Setting value of cache.ini to 1..."
    $a = Get-Content  $cachefolder\cache.ini
    $a  = 1
    Set-Content $a -Path $cachefolder\cache.ini

    #Starting timer service
    write-host "Starting timer service..."
    Get-Service SPTimerV4 -ComputerName $ServerName | Start-Service

    #Halt execution for 1 minute so that xml files can be re-generated fine
    write-host "Waiting for 1 min..."
    Sleep 60
    write-host "Clearing is done."
 }
}


Function Sp-GetWebStatistics{
<#
.SYNOPSIS
 Find all sites that have or have not been changed for a number of days

.DESCRIPTION
 Returns all sites that have not changed for a number of days and returns sites that are changed after the given date
 if no parameter is entered a default value of 30 days is used.

.EXAMPLE
Sp-GetWebStatistics

.EXAMPLE
Sp-GetWebStatistics -DaysPassed 60


.EXAMPLE
To return all sites that are in use since DaysPassed enter:

Sp-GetWebStatistics -DaysPassed 60 -ReturnUsedSites

.EXAMPLE
To return all sites that are in use since sharepoint was first installed:

Sp-GetWebStatistics -DaysPassed 10000 -ReturnUsedSites



.EXAMPLE
To get stats for a certain user, enter:

$MyArray = Sp-GetWebStatistics -DaysPassed 10000 -ReturnUsedSites

$MyArray | where {$_.Requester -eq "j.doe@contoso.com"} | select 'SiteName','Owners' | ft


#>
[Cmdletbinding()]
param
(
  [parameter(Mandatory=$false)]
  [int]$DaysPassed=30,
  [switch]$ReturnUsedSites
)
    $DateSinceDaysPassed=(get-date).AddDays((0-$DaysPassed))
    $DateSinceDaysPassed=$DateSinceDaysPassed.ToString("MM/dd/yyyy") 
    $SharepointDBs = Get-SPDatabase | where{(($_.type -like "content database") -and ($_.name -notlike "WSS*") -and ($_.name -notlike "SharePoint_AdminContent*"))} #| where {($_.name -like "Team*") -or ($_.name -like "Project*")}
    $global:WebsNotInUse=@()
    $global:WebsInUse=@()
    Log-text "Checking all sites that have not been changed since $DateSinceDaysPassed" -OnlyToScreen -ForegroundColor cyan
    Log-text "Variables global:WebsNotInUse and global:WebsInUse are always filled when running." -ForegroundColor cyan
    $backuperr=$ErrorActionPreference
    $ErrorActionPreference="silentlycontinue"
    $i=0
    Foreach($dbtest in $SharepointDBs){
        $i+=1
        Write-Progress -Activity "Enumerating sites" -Status "$i of $($SharepointDBs.Count) : $($dbtest.Name)" -PercentComplete (($i/$SharepointDBs.Count)*100)
        $site= get-spsite -ContentDatabase $dbtest.id -Limit all
        If (($site.url -like "$($Global:Server)/*") -and ($site.url -notlike "$($Global:mysiteHostUrl)/*")){
            foreach($web in $site.AllWebs){
                $size = ([math]::Round($web.Site.Usage.Storage/1MB).ToString())
                if ($web.LastItemModifiedDate -lt $DateSinceDaysPassed){
                    Log-text “Change more than $DaysPassed days ago: $($Web.Title)" -verboseLogging
                    $Owners=""
                     Log-text  "Owners:" -ForegroundColor yellow -verboseLogging
                    foreach($user in $web.SiteAdministrators){
                         Log-text  “$($user.Name),$($user.LoginName)” -verboseLogging
                        $Owners+="$($user.Name);"
                    }
                
                    $CurrentSite = New-Object psobject 
                    $CurrentSite | Add-Member NoteProperty -Name "SiteName" -Value "$($web.Title)"
                    $CurrentSite | Add-Member NoteProperty -Name "url" -Value "$($web.ServerRelativeUrl)"
                    $CurrentSite | Add-Member NoteProperty -Name "Database" -Value "$($dbtest.Name)"
                    $CurrentSite | Add-Member NoteProperty -Name "Requester" -Value "$($site.Owner.Email)"
                    $CurrentSite | Add-Member NoteProperty -Name "Owners" -Value "$Owners"
                    $CurrentSite | Add-Member NoteProperty -Name "Last-modified" -Value "$($web.LastItemModifiedDate)"
                    $CurrentSite | Add-Member NoteProperty -Name "Size" -Value "$size MB"
                    $global:WebsNotInUse+=$CurrentSite
                 }
                 else{
                 Log-text “Change less than $DaysPassed days ago: $($Web.Title)" -verboseLogging
                    $Owners=""
                     Log-text  "Owners:" -ForegroundColor yellow -verboseLogging
                    foreach($user in $web.SiteAdministrators){
                         Log-text  “$($user.Name),$($user.LoginName)” -verboseLogging
                        $Owners+="$($user.Name);"
                    }
                   $CurrentSite = New-Object psobject 
                    $CurrentSite | Add-Member NoteProperty -Name "SiteName" -Value "$($web.Title)"
                    $CurrentSite | Add-Member NoteProperty -Name "url" -Value "$($web.ServerRelativeUrl)"
                    $CurrentSite | Add-Member NoteProperty -Name "Database" -Value "$($dbtest.Name)"
                    $CurrentSite | Add-Member NoteProperty -Name "Requester" -Value "$($site.Owner.Email)"
                    $CurrentSite | Add-Member NoteProperty -Name "Owners" -Value "$Owners"
                    $CurrentSite | Add-Member NoteProperty -Name "Last-modified" -Value "$($web.LastItemModifiedDate)"
                    $CurrentSite | Add-Member NoteProperty -Name "Size" -Value "$size MB"
                    $global:WebsInUse+=$CurrentSite
                 }
            }#end foreach
        }
    }
    $ErrorActionPreference=$backuperr
    If($ReturnUsedSites -eq $false){
        If($global:WebsNotInUse.count -gt 0){
            Return, $global:WebsNotInUse
        }else{
            Return, "No unused sites within given timeframe"
        }
    }
    else{
        If($global:WebsInUse.count -gt 0){
            Return, $global:WebsInUse
        }else{
            Return, "No active sites within given timeframe"
        }
    }
}




Function sendmail($body) 
{ 
 $today = Get-Date 
 $SmtpClient = new-object system.net.mail.smtpClient  
 $mailmessage = New-Object system.net.mail.mailmessage  
 $SmtpClient.Host = "smtpserver.contoso.com" 
 $mailmessage.from = "SharePoint Server Mail -SharePoint Statistics <scriptingserver@contoso.com>"    
 $mailmessage.To.add("john.doe@contoso.com")
 $mailmessage.To.add("jane.doe@contoso.com")
 $mailmessage.Subject = “SharePoint Statistics”  
 $mailmessage.IsBodyHtml = $true 
 $mailmessage.Body = "<h4>Generated on : $today `n</h4><h3>Running on: $($env:COMPUTERNAME)</h3>" + $body 
 $smtpclient.Send($mailmessage)  
} 


Function SendUserMail{ 
<#
.SYNOPSIS
 Sends mail to user

.DESCRIPTION
 Sends email to user with html content contained in the body
 
 Emailaddress is the recipient and is mandatory
 Subject is a mandatory field
 Body is formed as an html type and can be created using the convertto-html command
 From is optional. It may be omitted. When not entered "ICT Server Mail"  will be the default sender


.EXAMPLE

SendUsermail -EmailAddress j.doe@contoso.com -Subject "Testmail" -Body $htmlcontent 

.EXAMPLE
Send local folder structure to user

$files= ls | ConvertTo-Html
SendUsermail -EmailAddress j.doe@contoso.com -Subject "My local files and folders" -Body $files

.EXAMPLE
Send local folder structure to user using a from field

$files= ls | ConvertTo-Html
SendUsermail -EmailAddress j.doe@contoso.com -Subject "My local files and folders" -Body $files -from FileSender@contoso.com


#>
param
(
[parameter(mandatory=$true)]
[string]$EmailAddress,
[parameter(mandatory=$true)]
[string]$Subject,
[parameter(mandatory=$true)]
$Body,
[parameter(mandatory=$false)]
[string]$From="ICT Server Mail <scriptingserver@contoso.com>"    
)

 $today = Get-Date 
 $SmtpClient = new-object system.net.mail.smtpClient  
 $mailmessage = New-Object system.net.mail.mailmessage  
 $SmtpClient.Host = "smtp.contoso.com" 
 $mailmessage.from = $From
 $mailmessage.To.add($EmailAddress)
 $mailmessage.Subject = $subject  
 $mailmessage.IsBodyHtml = $true 
 $mailmessage.Body = "<h4>Generated on : $today `n</h4>"+ $body 
 $smtpclient.Send($mailmessage)  
} 







Function Log-text{
  [CmdletBinding()]
  param
  (
    [Parameter(Mandatory=$false)]
    [string]$Logtext,
    [switch]$verboseLogging,
    [switch]$OnlyToScreen,
    [switch]$IsError,
    [string]$ForegroundColor="white"
  )
  
If(($global:LogMode -eq "normal") -and ($verboseLogging -eq $true)){return}

    If(($global:logToScreenOnly -eq $false)-and ($OnlyToScreen-eq $false)){$logtext | add-content $global:Logfile}
    If($global:LogToScreen -eq $true){
            if ($IsError -eq $false){
                write-Host $Logtext -ForegroundColor $ForegroundColor
            }else{
                write-Host $Logtext -ForegroundColor Red
            }
        } 
}


Function Sp-SendStatistics{
<#
.SYNOPSIS
Sends sharepoint statistics to the pfg
.DESCRIPTION
Sends statistics about site usage to the PFG
You can enter the number of days to accept as a maximum for "unused sites"

.EXAMPLE

Command will treat all sites not changed in 20 days as unused and send mail accordingly
 
Sp-Sendstatistics -dayspassed 20
#>
param(
[parameter(mandatory=$false)]
[int]$Dayspassed=30
)

    Sp-GetWebStatistics -DaysPassed $Dayspassed
    If($global:WebsNotInUse.Count -gt 0){
     $bodyme = $global:WebsNotInUse | Sort-Object "Last-modified","Requester" | ConvertTo-Html -Title "SharePoint stats" -Body "<H2><font face='calibri'<H2><br><br>Sites in use `n</H2> <H3>Not in use for $Dayspassed days:  $($global:WebsNotInUse.count)`n</H3> `n<H3><Br>" -head "<style>td{font-size:smaller;padding:0 0 0 5px;border: 1px solid #003366;}table{border: 1px solid #003366;margin:0;padding:0}tr{margin:0;padding:0;}h2{color:black};th{font-size:smaller;text-align:left;border: 1px solid #003366;background-color:#aaa;}</style>" | foreach {$_ -replace "<table>", "<table cellspacing=0></font>"} 
    }else
    {
        $bodyme = ConvertTo-Html -Title "SharePoint stats" -Body "<H2><font face='calibri'<H2><br><br>No unused sites found`n</H2><br>"
    }
    $bodyme+="<font face='calibri'> <H2><font face='calibri'><br><br>Sites in use `n</H2>"
    $bodyme+= $global:WebsInUse | Sort-Object "Last-modified","Requester" | ConvertTo-Html -Body "<font face='calibri'> <H3>Used in the last $Dayspassed days:  $($global:WebsInUse.count)`n</H3> `n<H3><Br>" -head "<style>td{font-size:smaller;padding:0 0 0 5px;border: 1px solid #003366;}table{border: 1px solid #003366;margin:0;padding:0}tr{margin:0;padding:0;}h2{color:black};th{font-size:smaller;text-align:left;border: 1px solid #003366;background-color:#aaa;}</style>" | foreach {$_ -replace "<table>", "<table cellspacing=0></font>"} 
    log-text "Sending mail..." -OnlyToScreen -ForegroundColor yellow
    sendmail $bodyme
    Log-text "Done." -OnlyToScreen -ForegroundColor yellow
    $bodyme > C:\temp\log.html
}	


Function SP-GetUserStats{
<#
.SYNOPSIS
Retrieves statistics about sites where users are the requester

.DESCRIPTION
Retrieves statistics about sites where users are the requester
Caution! This does not retrieve all sites where the user is an admin/owner.

Output is an array of all sites found for the user and an email with this information can be
sent using the -sendemail option. The results of the function will be e-mailed to the user emailaddress

You can also send the email to an alternet recipient. e-mail wil NOT be sent to the original emailaddress entered

.EXAMPLE
Get user stats
SP-GetUserStats -EmailAddress j.doe@contoso.com 

.EXAMPLE
Put information about user 'rataplan' in an array
$MyArray = SP-GetUserStats  -EmailAddress j.doe@contoso.com 

.EXAMPLE
Send email with site information to user 'rataplan' (and put results in an array at the same time)
$MyArray = SP-GetUserStats  -EmailAddress j.doe@contoso.com -SendEmail


.EXAMPLE
Send email with site information about user 'rataplan' to a manager
SP-GetUserStats  -EmailAddress j.doe@contoso.com -SendEmail -AlternateEmail manager@contoso.com

#>
    param(
    [parameter(mandatory=$true)]
    [String]$EmailAddress,
    [parameter(mandatory=$false)]
    [switch]$SendEmail=$false,
    [parameter(mandatory=$false)]
    [string]$AlternateEmail
    )
    Log-text "Retreiving websites requested by $Emailaddress"
    $MyArray = Sp-GetWebStatistics -DaysPassed 10000 -ReturnUsedSites
    $UsedMailaddress=$EmailAddress
    $result=@()
    $result = $MyArray | where {$_.Requester -eq $EmailAddress} 
    If($result.Count -gt 0){
        If($sendEmail -eq $false){
            Return, $result
        }else
        {
            If($AlternateEmail.IndexOf("@") -gt 0){
              $UsedMailaddress= $AlternateEmail
            }
            $body= $result| Sort-Object "Last-modified" | ConvertTo-Html -Title "SharePoint statistics for $emailaddress " -Body "<H2><font face='calibri'<H2> <H3>Total number of sites:  $($result.count)`n</H3> `n<H3><Br>" -head "<style>td{font-size:smaller;padding:0 0 0 5px;border: 1px solid #003366;}table{border: 1px solid #003366;margin:0;padding:0}tr{margin:0;padding:0;}h2{color:black};th{font-size:smaller;text-align:left;border: 1px solid #003366;background-color:#aaa;}</style>" | foreach {$_ -replace "<table>", "<table cellspacing=0></font>"} 
            SendUserMail -EmailAddress $UsedMailaddress -Body $body -Subject "SharePoint user statistics"
            log-text "Mail sent to $UsedMailaddress" -OnlyToScreen -ForegroundColor yellow
            Return, $result
        }
    }else
    {
    log-text "No sites found for $emailaddress" -ForegroundColor red
    Return, 0
    }
}





$local:Initok = SP-Initialise
if ($local:Initok -eq $true){
$local:Currentuser = [Environment]::UserName
Write-host "**********************************************" -ForegroundColor Yellow
Write-Host "ITMarco's SharePoint Commands loaded" -ForegroundColor Yellow
Write-host "Version 1.2" -ForegroundColor Magenta
Write-host "**********************************************" -ForegroundColor Yellow
Write-host
Write-Host "SharePoint environment values: " -ForegroundColor Yellow
Write-Host
Write-host "SharePoint target URL = "$Global:Server
Write-host "MySite Host URL = "  $Global:mysiteHostUrl 
Write-host "Database server = " $Global:DBServer 
Write-Host
Write-Host "Almost all commands have help available using get-help"
Write-Host
Write-Host "Available commands:" -ForegroundColor Yellow
get-command sp-* | select CommandType,Name | ft -AutoSize
Write-host "**********************************************" -ForegroundColor Yellow
}else{

Write-host "**********************************************" -ForegroundColor red
Write-Host "Failed to intialise SharePoint CommandLets" -ForegroundColor red
Write-host "**********************************************" -ForegroundColor red
Write-host
$Error


}
Write-Host "You are user:" $local:Currentuser -ForegroundColor Yellow


